package org.boyan.modules.cgreport.util;

import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.WithItem;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
 * @author: zhuxiaobao
 * @time: 2021/9/26 10:13
 */

public class CgReportSqlServerParse {
    private static final Log a = LogFactory.getLog(CgReportSqlServerParse.class);

    private static Pattern b = Pattern.compile(":[ tnx0Bfr]*[0-9a-z.A-Z_]+");

    private static String c = ".";

    private static String d = "@@@";

    public String a(String paramString) throws JSQLParserException {
        Statement statement = null;
        List<String> list = null;
        try {
            statement = CCJSqlParserUtil.parse(paramString);
        } catch (JSQLParserException jSQLParserException) {
            if (jSQLParserException.toString().contains(c)) {
                list = b(paramString);
                for (String str1 : list) {
                    paramString = paramString.replace(str1, str1.replace(c, d));
                }
                statement = CCJSqlParserUtil.parse(paramString);
            } else {
                jSQLParserException.printStackTrace();
            }
            a.debug(" --- JSQLParser with DIAN --- convert begin sql=" + paramString);
        }
        Select select = (Select)statement;
        SelectBody selectBody = select.getSelectBody();
        a(selectBody);
        String str = select.toString();
        if (list != null) {
            for (String str1 : list) {
                str = str.replace(str1.replace(c, d), str1.replace(d, c));
            }
            a.debug(" --- JSQLParser with DIAN --- convert end sql=" + paramString);
        }
        return str;
    }

    private static List<String> b(String paramString) {
        LinkedHashSet<String> linkedHashSet = new LinkedHashSet<>();
        Matcher matcher = b.matcher(paramString);
        while (matcher.find()) {
            if (matcher.group() != null && matcher.group().contains(c)) {
                linkedHashSet.add(matcher.group());
            }
        }
        return new ArrayList<>(linkedHashSet);
    }

    private void a(SelectBody paramSelectBody) {
        if (paramSelectBody instanceof PlainSelect) {
            a((PlainSelect)paramSelectBody);
        } else if (paramSelectBody instanceof WithItem) {
            WithItem withItem = (WithItem)paramSelectBody;
            if (withItem.getSelectBody() != null) {
                a(withItem.getSelectBody());
            }
        } else {
            SetOperationList setOperationList = (SetOperationList)paramSelectBody;
            if (setOperationList.getSelects() != null && setOperationList.getSelects().size() > 0) {
                List<SelectBody> list = setOperationList.getSelects();
                for (SelectBody selectBody : list) {
                    if (selectBody instanceof PlainSelect) {
                        a((PlainSelect)selectBody);
                        continue;
                    }
                    if (selectBody instanceof WithItem) {
                        WithItem withItem = (WithItem)selectBody;
                        if (withItem.getSelectBody() != null) {
                            a(withItem.getSelectBody());
                        }
                    }
                }
            }
            if (!a(setOperationList.getOrderByElements())) {
                setOperationList.setOrderByElements(null);
            }
        }
    }

    private void a(PlainSelect paramPlainSelect) {
        if (!a(paramPlainSelect.getOrderByElements())) {
            paramPlainSelect.setOrderByElements(null);
        }
        if (paramPlainSelect.getFromItem() != null) {
            a(paramPlainSelect.getFromItem());
        }
        if (paramPlainSelect.getJoins() != null && paramPlainSelect.getJoins().size() > 0) {
            List<Join> list = paramPlainSelect.getJoins();
            for (Join join : list) {
                if (join.getRightItem() != null) {
                    a(join.getRightItem());
                }
            }
        }
    }

    private void a(FromItem paramFromItem) {
        if (paramFromItem instanceof SubJoin) {
            SubJoin subJoin = (SubJoin)paramFromItem;
            if (subJoin.getJoinList() != null) {
                for (Join join : subJoin.getJoinList()) {
                    if (join.getRightItem() != null) {
                        a(join.getRightItem());
                    }
                }
            }
            if (subJoin.getLeft() != null) {
                a(subJoin.getLeft());
            }
        } else if (paramFromItem instanceof SubSelect) {
            SubSelect subSelect = (SubSelect)paramFromItem;
            if (subSelect.getSelectBody() != null) {
                a(subSelect.getSelectBody());
            }
        } else if (!(paramFromItem instanceof net.sf.jsqlparser.statement.select.ValuesList)) {
            if (paramFromItem instanceof LateralSubSelect) {
                LateralSubSelect lateralSubSelect = (LateralSubSelect)paramFromItem;
                if (lateralSubSelect.getSubSelect() != null) {
                    SubSelect subSelect = lateralSubSelect.getSubSelect();
                    if (subSelect.getSelectBody() != null) {
                        a(subSelect.getSelectBody());
                    }
                }
            }
        }
    }

    private boolean a(List<OrderByElement> paramList) {
        if (paramList == null) {
            return false;
        }
        for (OrderByElement orderByElement : paramList) {
            if (orderByElement.toString().toUpperCase().contains("?")) {
                return true;
            }
        }
        return false;
    }

    public static void main(String[] args) {
        String str1 = "select * from (select s.username,s.create_time,s.realname,jr.create_by,jr.`name` from sys_user s INNER JOIN jimu_report jr on s.username = jr.create_by where jr.type='chartinfo'  ORDER BY jr.create_time)a";
        String str2 = "select * from sys_user ORDER BY create_time,a,c desc";
        String str3 = "SELECT cf.DB_FIELD_NAME,cf.DB_FIELD_TXT FROM ONL_CGFORM_FIELD cf INNER JOIN ONL_CGFORM_HEAD ch ON cf.CGFORM_HEAD_ID = ch.ID WHERE ch.TABLE_NAME = :tableName ORDER BY cf.ORDER_NUM ";
        String str4 = "  select count(*) as visit\n        \t   ,count(distinct(ip)) as ip\n             ,CONVERT(varchar(100), create_time, 23) as tian\n        \t   ,RIGHT(CONVERT(varchar(100), create_time, 23),5) as type\n         from sys_log \n         where log_type = 1 and create_time >= :dayStart and create_time < :dayEnd \n         group by CONVERT(varchar(100), create_time, 23),RIGHT(CONVERT(varchar(100), create_time, 23),5)  \n         order by CONVERT(varchar(100), create_time, 23) \n asc\t  ";
        String str5 = "SELECT * FROM jimu_report jr WHERE 1=1 and jr.CREATE_BY = :jimuReport.createBy and jr.TYPE = :jimuReport.type and jr.DEL_FLAG = :jimuReport.delFlag and jr.TEMPLATE = :jimuReport.template ORDER BY jr.create_time DESC";
        String str6 = "SELECT count(*) FROM (SELECT * FROM sys_user order by id OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY) AS a";
        String str7 = "SELECT * FROM jimu_report as jr WHERE jr.create_by in (SELECT top 100 username FROM sys_user  ORDER BY create_time) ORDER BY create_time desc";
        String str8 = "select  a.*  from (SELECT top 100  jr.create_time,jr.name,jr.code from jimu_report jr LEFT JOIN sys_user s on jr.create_by = s.username ORDER BY  s.create_time) a ORDER BY  a.create_time ASC";
        String str9 = "select * from sys_user order by CASE WHEN sex='1' THEN create_time else update_time END";
        try {
            System.out.println(str9);
            System.out.println(((CgReportSqlServerParse)CgReportSqlServerParse.class.newInstance()).a(str9));
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}
